﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Security.Cryptography;
using System.Management;
using System.Windows.Forms;

namespace intelliPaper.DB.Util
{
    public class DBConnection
    {

        SqlConnection DBConn = null;
        SqlTransaction Transaction = null;

        #region 数据连接
        private SqlConnection getConn()
        {
            try
            {
                return new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["sqldbconn"].ConnectionString);
            }
            catch (SqlException se)
            {
                MessageBox.Show(se.Message);
                return null;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
        private void CloseConn(SqlConnection con)
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
                con = null;
            }
        }
        public void GetConn()
        {
            DBConn = getConn();
            DBConn.Open();
            Transaction = DBConn.BeginTransaction();
        }
        //事务处理
        public void CommitTransaction()
        {
            if (Transaction != null)
            {
                Transaction.Commit();
            }
            Transaction = null;
            CloseConn(DBConn);
        }
        #endregion


        public bool ExecuteSql(string sql)
        {
            SqlConnection con = getConn();
            con.Open();
            SqlCommand com = new SqlCommand(sql, con);
            int Ex = com.ExecuteNonQuery();
            con.Close();
            if (Ex > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }



        #region   增加数据
        //单条记录的新增
        public bool Insert(Dictionary<string, object> arr, string tableName)
        {
            try
            {
                if (Transaction == null) GetConn();
                string sql = "insert into " + tableName + "(";
                string sql2 = ") values (";

                SqlCommand com = new SqlCommand(sql, DBConn);
                com.Transaction = Transaction;
                //遍历集合，1:生成sql语句  2:给SqlCommand对像加参数
                foreach (KeyValuePair<string, object> ar in arr)
                {
                    sql += ar.Key + ",";
                    sql2 += "@" + ar.Key + ",";
                    SqlParameter sp = new SqlParameter("@" + ar.Key, ar.Value.GetType());
                    sp.Value = ar.Value;
                    com.Parameters.Add(sp);
                }
                //拼接成完整的sql语句
                sql = sql.Substring(0, sql.Length - 1) + sql2.Substring(0, sql2.Length - 1) + ")";
                //重新把完整的sql语句赋值给SqlCommand对象
                com.CommandText = sql;
                bool result = Convert.ToBoolean(com.ExecuteNonQuery());

                return result;

            }
            catch (Exception ex)
            {
                Transaction.Rollback();
                Transaction = null;
                if (DBConn != null) { CloseConn(DBConn); }
                Console.Write(ex.Message);
                return false;
            }
        }

        public int GetIdentity()
        {
            SqlCommand sqlCom = new SqlCommand("select @@IDENTITY", DBConn, Transaction);
            int result = Convert.ToInt32(sqlCom.ExecuteScalar());
            return result;

        }
        
        //批量新增
        public bool Insert(List<Dictionary<string, object>> arr, string tableName)
        {
            try
            {
                if (Transaction == null) GetConn();
                foreach (Dictionary<string, object> ar in arr)
                {
                    string sql = "insert into " + tableName + "(";
                    string sql2 = ")values(";

                    SqlCommand com = new SqlCommand(sql, DBConn);
                    com.Transaction = Transaction;////这行代码很重要
                    //遍历集合，1:生成sql语句  2:给SqlCommand对像加参数
                    foreach (KeyValuePair<string, object> a in ar)
                    {
                        sql += a.Key + ",";
                        sql2 += "@" + a.Key + ",";
                        SqlParameter sp = new SqlParameter("@" + a.Key, a.Value.GetType());
                        sp.Value = a.Value;
                        com.Parameters.Add(sp);
                    }
                    //拼接成完整的sql语句
                    sql = sql.Substring(0, sql.Length - 1) + sql2.Substring(0, sql2.Length - 1) + ")";
                    //重新把完整的sql语句赋值给SqlCommand对象
                    com.CommandText = sql;
                    Convert.ToBoolean(com.ExecuteNonQuery());
                }
                return true;
            }
            catch (Exception ex)
            {
                Transaction.Rollback();//回滚事物
                Transaction = null;
                if (DBConn != null) { CloseConn(DBConn); }
                Console.Write(ex.Message);
                return false;
            }
        }

        #endregion



        #region   数据删除
        //单条记录的删除
        public bool Delete(Dictionary<string, object> arr, string tableName)
        {
            try
            {
                if (Transaction == null) GetConn();
                string sql = "delete " + tableName + " where ";
                SqlCommand com = new SqlCommand(sql, DBConn);
                com.Transaction = Transaction;
                foreach (KeyValuePair<string, object> ar in arr)
                {
                    sql += ar.Key + "=@" + ar.Key + " and ";
                    SqlParameter sp = new SqlParameter("@" + ar.Key, ar.Value.GetType());
                    sp.Value = ar.Value;
                    com.Parameters.Add(sp);
                }

                sql = sql.Substring(0, sql.Length - 4);
                com.CommandText = sql;

                return Convert.ToBoolean(com.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                Transaction.Rollback();
                Transaction = null;
                if (DBConn != null) { CloseConn(DBConn); }
                Console.Write(ex.Message);
                return false;
            }

        }
        //批量删除
        public bool Delete(List<Dictionary<string, object>> arr, string tableName)
        {
            try
            {
                if (Transaction == null) GetConn();
                foreach (Dictionary<string, object> ar in arr)
                {
                    string sql = "delete " + tableName + " where ";
                    SqlCommand com = new SqlCommand(sql, DBConn);
                    com.Transaction = Transaction;//这个很重要

                    foreach (KeyValuePair<string, object> a in ar)
                    {
                        sql += a.Key + "=@" + a.Key + " and ";
                        SqlParameter sp = new SqlParameter("@" + a.Key, a.Value.GetType());
                        sp.Value = a.Value;
                        com.Parameters.Add(sp);
                    }

                    sql = sql.Substring(0, sql.Length - 4);
                    com.CommandText = sql;
                    com.ExecuteNonQuery();
                }

                return true;
            }
            catch (Exception ex)
            {
                Transaction.Rollback();
                Transaction = null;
                if (DBConn != null) { CloseConn(DBConn); }
                Console.Write(ex.Message);
                return false;
            }

        }
        #endregion

        #region   数据修改
        //单条记录的修改
        public bool Update(Dictionary<string, object> arr, string tableName)
        {
            try
            {
                if (Transaction == null) GetConn();
                string sql = "update " + tableName + " set ";
                SqlCommand com = new SqlCommand(sql, DBConn);
                com.Transaction = Transaction;

                foreach (KeyValuePair<string, object> ar in arr)
                {
                    sql += ar.Key + "=@" + ar.Key + ",";
                    SqlParameter sp = new SqlParameter("@" + ar.Key, ar.Value.GetType());
                    sp.Value = ar.Value;
                    com.Parameters.Add(sp);
                }
                sql = sql.Substring(0, sql.Length - 1) + " where ";

                //查找表的主键
                DataTable d = Select("sp_pkeys " + tableName);
                foreach (DataRow rw in d.Rows)
                {
                    sql += rw[3].ToString() + "=@" + rw[3].ToString() + " and ";
                }
                sql = sql.Substring(0, sql.Length - 4);

                com.CommandText = sql;

                return Convert.ToBoolean(com.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                Transaction.Rollback();
                Transaction = null;
                if (DBConn != null) { CloseConn(DBConn); }
                Console.Write(ex.Message);
                return false;
            }
        }


        public bool Update(Dictionary<string, object> values, Dictionary<string, object> conditions, string tablename)
        {
            try
            {
                if (Transaction == null) GetConn();
                string sqlString = "update " + tablename + " set ";
                SqlCommand sc = new SqlCommand();
                sc.Connection = DBConn;
                sc.Transaction = Transaction;
                foreach (KeyValuePair<string, object> kv in values)
                {
                    sqlString += kv.Key + "=@" + kv.Key + ",";
                    SqlParameter sp = new SqlParameter("@" + kv.Key, kv.Value);
                    sc.Parameters.Add(sp);
                }
                sqlString = sqlString.Substring(0, sqlString.Length - 1) + " where ";

                foreach (KeyValuePair<string, object> kv in conditions)
                {
                    sqlString += kv.Key + "=@" + kv.Key + "and ";
                    SqlParameter sp = new SqlParameter("@" + kv.Key, kv.Value.GetType ());
                    sp.Value = kv.Value;
                    sc.Parameters.Add(sp);
                }

                sqlString = sqlString.Substring(0, sqlString.Length - 4);

                sc.CommandText = sqlString;

                return sc.ExecuteNonQuery() > 0 ? true : false;
            }
            catch (Exception ex)
            {
                Transaction.Rollback();
                Transaction = null;
                if (DBConn != null) { CloseConn(DBConn); }
                Console.Write(ex.Message);
                return false;
            }

        }
        //批量修改
        public bool Update(List<Dictionary<string, object>> li, string tableName)
        {
            try
            {
                if (Transaction == null) GetConn();
                foreach (Dictionary<string, object> di in li)
                {
                    string sql = "update " + tableName + " set ";
                    SqlCommand cmd = new SqlCommand(sql, DBConn);
                    cmd.Transaction = Transaction;
                    foreach (KeyValuePair<string, object> ky in di)
                    {
                        sql += ky.Key + "=@" + ky.Key + ",";
                        SqlParameter sq = new SqlParameter("@" + ky.Key, ky.Value.GetType());
                        sq.Value = ky.Value;
                        cmd.Parameters.Add(sq);
                    }
                    sql = sql.Substring(0, sql.Length - 1) + " where ";
                    string sql1 = "sp_pkeys " + tableName;
                    DataTable dt = Select(sql1);
                    foreach (DataRow dr in dt.Rows)
                    {
                        sql += dr[3] + "=@" + dr[3] + " and ";
                    }
                    sql = sql.Substring(0, sql.Length - 4);
                    cmd.CommandText = sql;

                    cmd.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                Transaction.Rollback();
                Transaction = null;
                if (DBConn != null) { CloseConn(DBConn); }
                Console.WriteLine(ex.Message);
                return false;
            }
        }
        #endregion

        #region   查询方法返回DataTable
        public DataTable Select(string sql)
        {
            try
            {
                if (Transaction == null) GetConn();
                SqlDataAdapter dt = new SqlDataAdapter(sql, DBConn);
                dt.SelectCommand.Transaction = Transaction;
                DataTable d = new DataTable();

                dt.Fill(d);

                return d;
            }
            catch (Exception ex)
            {
                Transaction.Rollback();
                Transaction = null;
                if (DBConn != null) { CloseConn(DBConn); }
                Console.Write(ex.Message);
                return null;
            }

        }
        #endregion

        #region   查询方法返回List集合
        public List<Dictionary<string, object>> SelectToList(string sql)
        {
            try
            {
                DBConn = getConn();
                SqlDataAdapter dt = new SqlDataAdapter(sql, DBConn);

                DataTable d = new DataTable();
                dt.Fill(d);

                List<Dictionary<string, object>> arr = new List<Dictionary<string, object>>();
                //遍历DataTable 根据 DataTable 中的DataRow 来生成 Dictionary<string, object>
                //外层循环遍历DataTable中的行
                foreach (DataRow rw in d.Rows)
                {
                    //内层循环遍历DataTable的列结构
                    Dictionary<string, object> ar = new Dictionary<string, object>();
                    foreach (DataColumn dc in d.Columns)
                    {
                        //列名做为键名
                        //通过列名来得到行(DataRow)中的数据    
                        //if (rw[dc.ColumnName].GetType().FullName != "System.DateTime")
                        //{
                        //    ar.Add(dc.ColumnName, rw[dc.ColumnName]);
                        //}
                        //else
                        //{
                        //    ar.Add(dc.ColumnName, ((DateTime)rw[dc.ColumnName]).ToShortDateString());
                        //}

                        ar.Add(dc.ColumnName, rw[dc.ColumnName]);

                    }
                    arr.Add(ar);
                }
                return arr;

            }
            catch (Exception ex)
            {
                if (DBConn != null) { CloseConn(DBConn); }
                Console.Write(ex.Message);
                return null;
            }
        }
        #endregion



        //(2012-01-13)
        #region 密码加密类
        public string Encrypt(string password)
        {
            SHA1 s = new SHA1CryptoServiceProvider();
            byte[] data = System.Text.Encoding.Default.GetBytes(password);//将字符编码为一个字节序列 
            byte[] sha1data = s.ComputeHash(data);//计算data字节数组的哈希值 
            s.Clear();
            string str = "";
            for (int i = 0; i < sha1data.Length; i++)
            {
                str += sha1data[i].ToString("x").PadLeft(2, '0');
            }
            return str;
        }
        #endregion

        /// <summary>
        /// 根据SQL语句返回DataSet
        /// </summary>
        /// <param name="sqlCmd">SQL语句</param>
        /// <returns>数据集</returns>
        public DataSet GetDataSet(string sqlCmd)
        {

            SqlConnection Conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["com"].ConnectionString);

            DataSet DS = new DataSet();

            try
            {

                Conn.Open();

                SqlDataAdapter DA = new SqlDataAdapter(sqlCmd, Conn);

                DA.Fill(DS, "ds");

            }

            catch (System.Data.SqlClient.SqlException E)
            {

                throw new Exception(E.Message);

            }

            return DS;

        }



        public SqlDataReader getRow(string sql)
        {
            SqlConnection con = getConn();
            con.Open();
            SqlCommand com = new SqlCommand(sql, con);
            SqlDataReader sdr = com.ExecuteReader();
            return sdr;
        }

    }
}
